#!/usr/local/python3/bin/python3
# coding=utf-8
import os
import xlrd
from openpyxl import *
import pymysql

class SqlOpertaion(object):
    insert_unit_sql = "INSERT INTO %s.config_unit(id, name) VALUES (%s, '%s');"
    insert_sub_unit_sql = "INSERT INTO %s.config_unit(id, name, parent_id) VALUES (%s, '%s', %s);"
    insert_event_sql ="INSERT INTO %s.config_event(camera_id, event_type_id, is_enabled, active_start_time, active_end_time, alarm_type, alarm_level, params) " \
                    "VALUES (%s, %s, 1, '1990-01-01 00:00:00', '1990-01-01 23:59:59', '1|2|', 2, '%s');"
    insert_first_area_sql = "INSERT INTO %s.config_detection_area(camera_id, event_type_id, points, direction) " \
                        "VALUES (%s, %s, '%s', 1);"
    insert_second_area_sql = "INSERT INTO %s.config_detection_area(camera_id, event_type_id, points, direction) " \
                        "VALUES (%s, %s, '%s', 1);"
    insert_camera_sql_forautotest = "INSERT INTO %s.config_camera(name, ip, port, username, password, streamserver_id, manufacture, access_type, type, unit_id) " \
                                "VALUES ('%s', '%s', %s, '%s', '%s', 10000, 2, 100, 1, %s);"
    insert_train_6a_sql_forautotest = "INSERT INTO %s.realtime_train_6a(code, status, address) " \
                                    "VALUES (\"%s\", 0, \"%s\");"
    select_sql = "select count(1) from realtime_event where processing_state = 2 and event_type_id = %s and time between '%s' and '%s';"
    drop_sql="truncate table %s;"

    def __init__(self, ip, username, password, database_name):
        self.connect=pymysql.connect(host=ip, user=username, password=password, database=database_name, charset="utf8mb4")
        self.cursor=self.connect.cursor()

    def edit(self, sql):
        try:
            if isinstance(sql, list):
                for i in sql:
                    self.cursor.execute(i)
            else:
                self.cursor.execute(sql)
        except Exception as e:
            # logger.error(f"failed to edit sql: {e}")
            self.connect.rollback()
        else:
            self.connect.commit()

    def find(self, sql):
        self.cursor.execute(sql)
        actual_result=self.cursor.fetchone()[0]
        return actual_result

    def close(self):
        self.cursor.close()
        self.connect.close()

class SheetTypeError(Exception):
    pass

class RowIndexError(Exception):
    pass

class ExcelOperation(object):
    def __init__(self, file):
        if os.path.exists(file):
            self.file=file
        else:
            raise FileNotFoundError(f"{file} does not exist!")

    def get_data(self, locate=0, element_dict=True, dict=False):
        """
        获取数据
        locate: 定位sheet页，可以是索引，也可以是sheet页名称
        element_dict: 获取的元素是否为字典类型
        dict: 获取的数据的类型是否为字典类型
         """
        self.locate = locate
        self.element_dict = element_dict
        self.workbook=xlrd.open_workbook(self.file)

        if type(locate) not in [int, str]:
            raise SheetTypeError(f"Please enter <type int> or <type str>, not {type(locate)}")
        elif type(locate) == int:
            self.sheet=self.workbook.sheet_by_index(locate)
        else:
            self.sheet=self.workbook.sheet_by_name(locate)

        # 元素类型是字典，元素组合成列表
        if element_dict and not dict:
            keys=self.sheet.row_values(0)
            list=[]
            for i in range(1, self.sheet.nrows):
                dict={}
                for j in range(0, self.sheet.ncols):
                    dict[keys[j]]=self.sheet.cell_value(i, j)
                list.append(dict)
            return list
        # 元素类型是列表，元素组合成列表
        elif not element_dict and not dict:
            list=[]
            for i in self.sheet.nrows:
                list.append(self.sheet.row_values(i))
            return list
        # 元素类型是键值对，元素组合成字典
        elif element_dict and dict:
            dict={}
            for i in range(1, self.sheet.nrows):
                dict[self.sheet.cell_value(i, 0)]=self.sheet.cell_value(i, 1)
            return dict

    def clear_data(self, locate, start_row, end_row=-1):
        """
        按行清空数据
        locate: 定位sheet页，只能是sheet页名称
        start_row: 从哪一行开始删除
        end_row: 到哪一行结束删除
        """
        write_book = load_workbook(self.file)
        write_sheet = write_book.get_sheet_by_name(locate)  # write_book.active
        num_row = write_sheet.max_row
        if end_row == -1:
            write_sheet.delete_rows(start_row, num_row - 1)
        elif end_row >= start_row and start_row > 0:
            write_sheet.delete_rows(start_row, end_row)
        else:
            raise RowIndexError(f"Cannot delete data due to start_row: {start_row} or end_row: {end_row}")
        write_book.save(self.file)

    def write_data(self, locate, data):
        """
        在已存在文件上进行写操作，即追加形式
        locate: 定位sheet页，可以是索引，也可以是sheet页名称
        data: 待写入的数据，列表类型保存每个待写入的元素
        """
        workbook=load_workbook(self.file)
        if type(locate) == str:
            worksheet=workbook.get_sheet_by_name(locate)
        else:
            raise SheetTypeError(f"Please enter <type str>, not {type(locate)}")
        worksheet.append(data)
        workbook.save(self.file)

if __name__=="__main__":
    pass